<?php
error_reporting(E_ALL ^ E_NOTICE);
set_time_limit(0);
$db_name = "dinocap2";

$table_name = "dinocap2hd";

$connection = @mysql_connect("localhost","root","root") or die(mysql_error());

$db = @mysql_select_db($db_name, $connection) or die(mysql_error());


/*
  $value=’’;

  Foreach($_POST["teach"] as $key)

  { $value.=$key.’,’;}

 */
$logdate = mysql_query("SELECT DISTINCT whatday FROM `dinocap2hd`");
//$deviceid = mysql_query("SELECT DISTINCT deviceid FROM `dinocap2hd`");

//$everylogdate = mysql_fetch_array($logdate);
//$everylogdate = '2012-03-15';
//$n=0;
//$data = array();
while ($everylogdate = mysql_fetch_array($logdate)) {
 



    //weekPayARPU 周付费用户ARPU值 =周付费总额/周付费人数
    //总额
    //$weekPayNums_query = mysql_query("SELECT count(dayIapMoney)  FROM  dinocap2hd  where dayIapMoney >0 and  DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(whatday) and whatday = '$everylogdate[whatday]'");
    // $weekPayNums = mysql_fetch_array($weekPayNums_query);
    //$weekPayARPU_query = mysql_query("select '$weekPayNums[0]'/'$weekPayPesonrNum[0]'");
    $weekPayARPU_query = mysql_query("select (select sum(dayIapMoney) from dinocap2hd where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( now( ) ))/(select count(distinct deviceid) from dinocap2hd where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( now( ) ) and dayIapMoney > 0)");

     $weekPayARPU = mysql_fetch_array($weekPayARPU_query);
   // var_dump($weekPayARPU);die;
  //  $weekPayARPU =11;
        //weekActiveARPU 周活跃用户ARPU值
    //$weekActiveARPU_query = mysql_query("select '$weekPayNums[0]'/'$weekActivePesonrNum[0]'");
    $weekActiveARPU_query = mysql_query("select (select sum(dayIapMoney) from dinocap2hd where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( now( ) ))/(select count(distinct deviceid) from dinocap2hd where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( now( ) ) and dayPlayTime >2)");
    $weekActiveARPU = mysql_fetch_array($weekActiveARPU_query);
   
    // $weekActiveARPU = 0;
    //weekLogARPU 周登陆用户ARPU值  = 周付费用户/周登陆人数(活跃用户+新用户)  
    //周登陆人数
    //  $weekLodPesonrNums_query = mysql_query("SELECT count(deviceid)  FROM  dinocap2hd  where  DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(whatday) and whatday = '$everylogdate[whatday]'");
    $weekLodPesonrNums_query = mysql_query("select (select count(dayIapMoney) from dinocap2hd where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( now( ) ))/(select count(distinct deviceid) from dinocap2hd where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( now( ) ))");
    $weekLodPesonrNums = mysql_fetch_array($weekLodPesonrNums_query);
     // $weekLodPesonrNums = 0;
    //
    $weekLogARPU_query = mysql_query("select (select count(dayIapMoney) from dinocap2hd where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( now( ) ))/(select count(distinct deviceid) from dinocap2hd where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( now( ) ))");
    $weekLogARPU = mysql_fetch_array($weekLogARPU_query);
    //$weekLogARPU = 0;
    //weekWastageRate  周活跃用户流失率 上周活跃用户在本周没有登陆过游戏的用户数，除以上周的活跃人数。    not exists  //not in
    $weekWastageRate_query = mysql_query("select (select sum(deviceid) from dinocap2hd where (SELECT distinct deviceid FROM dinocap2hd   WHERE YEARWEEK(date_format(whatday,'%Y-%m-%d')) = YEARWEEK(now())-1 and dayIapMoney >0 and dayPlayTime >2) not in (SELECT  distinct deviceid FROM dinocap2hd WHERE YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( now( ) )))/(SELECT count(distinct deviceid) FROM dinocap2hd   WHERE YEARWEEK(date_format(whatday,'%Y-%m-%d')) = YEARWEEK(now())-1 and dayIapMoney >0 and dayPlayTime >2)");
    $weekWastageRate = mysql_fetch_array($weekWastageRate_query);
   //  $weekWastageRate = 0;
    //weekPayRate  周活跃用户付费率 周付费用户数除以周活跃人数
    //$weekPayRate_query = mysql_query("select '$weekPayPesonrNum[0]'/'$weekActivePesonrNum[0]'");
    $weekPayRate_query = mysql_query("select (select count(distinct deviceid) from dinocap2hd where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( now( ) ) and dayIapMoney > 0)/(SELECT count(distinct deviceid) FROM dinocap2hd   WHERE YEARWEEK(date_format(whatday,'%Y-%m-%d')) = YEARWEEK(now())-1 and dayIapMoney >0 and dayPlayTime >2)");
    $weekPayRate = mysql_fetch_array($weekPayRate_query);
    //$weekPayRate = 0;
    //weekLogPayRate周登陆用户付费率 周付费用户数除以周登陆人数
    $weekLogPayRate_query = mysql_query("select (select count(distinct deviceid) from dinocap2hd where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( now( ) ) and dayIapMoney > 0)/(select count(distinct deviceid) from dinocap2hd where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( now( ) ))");
    $weekLogPayRate = mysql_fetch_array($weekLogPayRate_query);
   // $weekLogPayRate = 0;
    //weekPayWastageRate  周付费用户流失率 上周有付费行为而本周7天未登陆的用户数除以上周的付费用户数    //sql查询慢,需要优化
    //SELECT count(id)  FROM  dinocap2hd WHERE YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( now( ) ) -1 and dayIapMoney >0 and whatday = '2012-03-28' where 
    $weekPayWastageRate_query = mysql_query("select (select count(deviceid) FROM  dinocap2hd where (SELECT distinct deviceid FROM dinocap2hd   WHERE YEARWEEK(date_format(whatday,'%Y-%m-%d')) = YEARWEEK(now())-1 and dayIapMoney >0) not in (SELECT  distinct deviceid FROM dinocap2hd WHERE YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( now( ) )))/(SELECT distinct deviceid FROM dinocap2hd   WHERE YEARWEEK(date_format(whatday,'%Y-%m-%d')) = YEARWEEK(now())-1 and dayIapMoney >0)");
    $weekPayWastageRate = mysql_fetch_array($weekPayWastageRate_query);
    //$weekPayWastageRate  = 0;
    //   var_dump($weekPayWastageRate);die;
    //本周总充值笔数除
     $weekpayTimes_query = mysql_query("SELECT count(id)  FROM  dinocap2hd  where  dayIapMoney >0 and  DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(whatday) and whatday = '$everylogdate[whatday]'");
     $weekpayTimes = mysql_fetch_array($weekpayTimes_query);
    // $weekpayTimes = 0;

    //$weekPayPesonrTime_query = mysql_query("select '$weekpayTimes[0]'/'$weekPayPesonrNum[0]'");
    //$weekPayPesonrTime = mysql_fetch_array($weekPayPesonrTime_query);
    //$weekPayPesonrTime = 0; //周付费用户人均充值笔数 (本周总充值笔数除以付费用户人数)
    $weekPayPesonrTime_query = mysql_query("SELECT (SELECT sum(dayPlayTime)  FROM  dinocap2hd  where  dayIapMoney >0 and  DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(whatday) and whatday = '$everylogdate[whatday]')/(SELECT count(id)  FROM  dinocap2hd  where  dayIapMoney >0 and  DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(whatday) and whatday = '$everylogdate[whatday]')");
    $weekPayPesonrTime = mysql_fetch_array($weekPayPesonrTime_query);
    //$weekPayPesonrTime = 0;
    //var_dump($weekPayPesonrTime);die;

  //  $firstlogindata = mysql_query("SELECT DISTINCT firstlogindata whatday FROM `dinocap2hd`");

    //var_dump($dayActivePesonrNum);  //前一天select * from bean where date(usedate) = date_sub(curdate(),interval 1 day);


    /* while( $deviceids = mysql_fetch_array($deviceid))
      {
      //$deviceids = mysql_fetch_array($deviceid);
      $player1DayExp_query = mysql_query(" select count(player1DayExp) from dinocap2hd where whatday = '2012-03-25' and (select player1DayExp from dinocap2hd where whatday = '2012-03-15')>any(select player1DayExp from dinocap2hd
      where date(whatday)  = date_sub('2012-03-15',interval 1 day) and deviceid  ='$deviceids[deviceid]'");
      $player1DayExp = mysql_fetch_array($player1DayExp_query);
      //  $player1DayExp = 0;
      // var_dump($player1DayExp[0]);
      //die;
      } */
 

    // var_dump($player5Level[0]);die;
//{
//}
//$data[] = $everylogdate;  
//$a = $loginPersonNum;


   /* $sql = "REPLACE INTO report (weekPayARPU,weekActiveARPU,weekLogARPU,
                          weekPayRate,weekPayPesonrTime,weekPayWastageRate,weekWastageRate) 
                  VALUES('$weekPayARPU[0]','$weekActiveARPU[0]','$weekLogARPU[0]',
                  '$weekPayRate[0]','$weekPayPesonrTime[0]','$weekPayWastageRate[0]','$weekWastageRate[0]')";*/
                 $sql = "UPDATE report SET weekPayARPU='$weekPayARPU[0]' ,weekActiveARPU ='$weekActiveARPU[0]',
                          weekLogARPU = '$weekLogARPU[0]',weekPayRate = '$weekPayRate[0]',weekPayPesonrTime ='$weekPayPesonrTime[0]',
                          weekPayWastageRate = '$weekPayWastageRate[0]',weekWastageRate = '$weekWastageRate[0]'
WHERE logdate='$everylogdate[0]'"; 

$query= @mysql_query($sql,$connection) or die(mysql_error());  


//var_dump($sql);die;
//$sql="insert into report values (‘$_POST[id]’,’$value’)";  

  //  $query = @mysql_query($sql, $connection) or die(mysql_error());

//$n++;
}
//var_dump($a);die;
//REPLACE

/*
  error_reporting(E_ERROR | E_WARNING | E_PARSE);
  mysql_connect(localhost, root, root);
  @mysql_select_db(dinocap2) or die("Unable to select database");

  $logdate =  mysql_query("SELECT DISTINCT whatday FROM `dinocap2hd`");

  while($everylogdate = mysql_fetch_array($logdate))
  {
  // var_dump($count);

  $totalisjailbreak = mysql_query("SELECT count(isjailbreak) whatday FROM `dinocap2hd` where isjailbreak = '1' and whatday = $everylogdate");
  //var_dump($$totalisjailbreak[0]);die;
  $loginPersonNum =  mysql_query("SELECT count(id) whatday FROM `dinocap2hd` where whatday ='$everylogdate'");
  $totalDayTotalTime = mysql_query("SELECT sum(dayTotalTime) whatday FROM `dinocap2hd` where whatday ='$everylogdate'");


  }
  //REPLACE
  mysql_query("INSERT INTO report (isjailbreak,loginPersonNum,totalDayTotalTime,logdate)
  VALUES('$totalisjailbreak','$loginPersonNum','$totalDayTotalTime','$everylogdate')");


 */
?>
